/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DataBase;

import Control.Control;
import Logic.Regla;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Scanner;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Fernando
 */
public class ServicioMenu extends Servicio {

    private String user;
    private String pass;
    private String ip;
    private String port;
    private String db;
    private static String GET_TABLE_COLUMNS = "select column_name from all_tab_columns where table_name=?";
    private static String GET_TABLES_USER = "select TABLE_NAME,OWNER from all_tables where owner!='SYS' and owner!='SYSTEM' and owner!='OUTLN' and owner!='TSMSYS' and owner!='DBSNMP' and owner!='CTXSYS' and owner!='XDB' and owner!='MDSYS' and owner!='HR' and owner!='FLOWS_FILES' and owner!='FLOWS_020100'";
    private static String SELECT_DB_LINKS = "select DB_LINK from user_db_links";
    private static String GET_USUARIOS = "select username from dba_users where lock_date is null order by username";
    private static String SELECT_TS = "select tablespace_name from dba_tablespaces where tablespace_name != 'SYSTEM' and tablespace_name != 'SYSAUX' and tablespace_name != 'TEMP' and tablespace_name != 'UNDO' and tablespace_name != 'USERS'";
    private static String CREATE_ROLE = "create role ";
    private static String CREATE_USER = "create user ";
    private static String DROP_USER = "drop user ";
    private static String DELETE_ROLE = "drop role ";
    private static String GRANT = "grant ";
    private static String USERROL = "select * from DBA_ROLE_PRIVS where grantee=?";
    private static String TABLES_TS = "select table_name from all_tables where tablespace_name = ?";
    private static String COLUMN_TYPE = "select data_type from all_tab_columns where table_name = ? and COLUMN_NAME = ?";
    private static String DROP_DBLINK="drop database link ";

    public ServicioMenu(String user, String pass, String ip, String port, String db) {
        this.user = user;
        this.pass = pass;
        this.ip = ip;
        this.port = port;
        this.db = db;

    }

    public boolean testConection() {
        try {
            conectar(user, pass, ip, port, db);
        } catch (SQLException | ClassNotFoundException ex) {
            Logger.getLogger(ServicioMenu.class.getName()).log(Level.SEVERE, null, ex);
        }
        if (this.conexion == null) {
            return false;
        } else {
            return true;
        }
    }
    
    public boolean dropDBLINK(String dblink) throws SQLException, ClassNotFoundException {
        boolean result = true;

        conectar(user, pass, ip, port, db);
        PreparedStatement pstmt = null;
        pstmt = conexion.prepareStatement(DROP_DBLINK+dblink);
        result = pstmt.execute();

        if (pstmt != null) {
            pstmt.close();

        }

        desconectar();
        return result;
    }

    public boolean grantPrivilege(String privilege, String role) throws SQLException, ClassNotFoundException {
        boolean result = true;

        conectar(user, pass, ip, port, db);
        PreparedStatement pstmt = null;
        System.out.println(GRANT + privilege + " to " + role);
        pstmt = conexion.prepareStatement(GRANT + privilege + " to " + role);
        result = pstmt.execute();

        if (pstmt != null) {
            pstmt.close();

        }

        desconectar();
        System.out.println(privilege + " granted to " + role);
        return result;
    }

    public boolean grantPrivilegeC(String privilege, String table, String column, String role) throws SQLException, ClassNotFoundException {
        boolean result = true;

        conectar(user, pass, ip, port, db);
        PreparedStatement pstmt = null;
        System.out.println(GRANT + privilege + " (" + column + ") on " + table + " to " + role);
        pstmt = conexion.prepareStatement(GRANT + privilege + " on " + table + "(" + column + ") " + " to " + role);
        result = pstmt.execute();

        if (pstmt != null) {
            pstmt.close();

        }

        desconectar();
        System.out.println(privilege + " on " + table + "(" + column + ")" + " granted to " + role);
        return result;
    }

    public boolean grantPrivilegeT(String privilege, String table, String role) throws SQLException, ClassNotFoundException {
        boolean result = true;

        conectar(user, pass, ip, port, db);
        PreparedStatement pstmt = null;
        System.out.println(GRANT + privilege + " on " + table + " to " + role);
        pstmt = conexion.prepareStatement(GRANT + privilege + " on " + table + " to " + role);
        result = pstmt.execute();

        if (pstmt != null) {
            pstmt.close();

        }
        System.out.println(privilege + " on " + table + " granted to " + role);
        desconectar();
        return result;
    }

    public boolean createRole(String role) throws SQLException, ClassNotFoundException {
        boolean result = true;

        conectar(user, pass, ip, port, db);
        PreparedStatement pstmt = null;

        pstmt = this.conexion.prepareStatement(CREATE_ROLE + role);
        result = pstmt.execute();

        if (pstmt != null) {
            pstmt.close();
        }
        desconectar();
        System.out.println("Role '" + role + "' created...");
        return result;
    }

    public boolean dropRole(String role) throws SQLException, ClassNotFoundException {
        boolean result = true;

        conectar(user, pass, ip, port, db);
        PreparedStatement pstmt = null;

        pstmt = this.conexion.prepareStatement(DELETE_ROLE + role);
        result = pstmt.execute();

        if (pstmt != null) {
            pstmt.close();

        }
        desconectar();
        System.out.println("Role '" + role + "' dropped...");
        return result;
    }

    public boolean createUser(String username, String password) throws SQLException, ClassNotFoundException {
        boolean result = true;

        conectar(user, pass, ip, port, db);
        PreparedStatement pstmt = null;

        pstmt = this.conexion.prepareStatement(CREATE_USER + username + " identified by " + password);
        result = pstmt.execute();

        if (pstmt != null) {
            pstmt.close();
        }
        desconectar();
        return result;
    }

    public boolean dropUser(String username) throws SQLException, ClassNotFoundException {
        boolean result = true;

        conectar(user, pass, ip, port, db);
        PreparedStatement pstmt = null;

        pstmt = this.conexion.prepareStatement(DROP_USER + username);
        result = pstmt.execute();

        if (pstmt != null) {
            pstmt.close();
        }
        desconectar();
        return result;
    }

    public ArrayList getTS() {

        try {

            conectar(user, pass, ip, port, db);
            System.out.println("Connection to DB successful");

        } catch (SQLException | ClassNotFoundException ex) {
            Logger.getLogger(ServicioMenu.class.getName()).log(Level.SEVERE, null, ex);
        }

        ResultSet rs = null;
        ArrayList list = new ArrayList();
        Statement stmt = null;
        try {
            stmt = conexion.createStatement();
            rs = stmt.executeQuery(SELECT_TS);
            while (rs.next()) {
                String aux = rs.getString("tablespace_name");
                list.add(aux);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ServicioMenu.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                desconectar();
            } catch (SQLException e) {
                //throw new GlobalException("Estatutos invalidos o nulos");
            }
        }


        return list;
    }

    public ArrayList getDBLinks() {

        try {

            conectar(user, pass, ip, port, db);
            System.out.println("Connection to DB successful");

        } catch (SQLException | ClassNotFoundException ex) {
            Logger.getLogger(ServicioMenu.class.getName()).log(Level.SEVERE, null, ex);
        }

        ResultSet rs = null;
        ArrayList list = new ArrayList();
        Statement stmt = null;
        try {
            stmt = conexion.createStatement();
            rs = stmt.executeQuery(SELECT_DB_LINKS);
            while (rs.next()) {
                String aux = rs.getString("DB_LINK");
                list.add(aux);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ServicioMenu.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                desconectar();
            } catch (SQLException e) {
                //throw new GlobalException("Estatutos invalidos o nulos");
            }
        }


        return list;
    }

    public Collection listarColumnas(String nombreTabla) {
        try {
            conectar(user, pass, ip, port, db);
        } catch (ClassNotFoundException ex) {
            //throw new GlobalException("No se ha localizado el Driver");
        } catch (SQLException e) {
            ///throw new NoDataException("La base de datos no se encuentra disponible");
        }

        ResultSet rs = null;
        ArrayList coleccion = new ArrayList();
        PreparedStatement stmt = null;
        try {
            stmt = conexion.prepareStatement(GET_TABLE_COLUMNS);
            stmt.setString(1, nombreTabla);
            rs = stmt.executeQuery();
            while (rs.next()) {
                String aux = rs.getString("column_name");
                coleccion.add(aux);
            }
        } catch (SQLException e) {
            e.printStackTrace();

            //throw new GlobalException("Sentencia no valida");
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                desconectar();
            } catch (SQLException e) {
                //throw new GlobalException("Estatutos invalidos o nulos");
            }
        }
        if (coleccion == null || coleccion.size() == 0) {
            //throw new NoDataException("No hay datos");
        }
        return coleccion;
    }

    public Collection listarTablas() {
        try {
            conectar(user, pass, ip, port, db);
        } catch (ClassNotFoundException ex) {
            //throw new GlobalException("No se ha localizado el Driver");
        } catch (SQLException e) {
            //throw new NoDataException("La base de datos no se encuentra disponible");
        }

        ResultSet rs = null;
        ArrayList coleccion = new ArrayList();
        Statement stmt = null;
        try {
            stmt = conexion.createStatement();
            rs = stmt.executeQuery(GET_TABLES_USER);
            while (rs.next()) {
                String aux = rs.getString("table_name");
                coleccion.add(aux);
                aux = rs.getString("owner");
                coleccion.add(aux);
            }
        } catch (SQLException e) {
            e.printStackTrace();

            //throw new GlobalException("Sentencia no valida");
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                desconectar();
            } catch (SQLException e) {
                //throw new GlobalException("Estatutos invalidos o nulos");
            }
        }
        if (coleccion == null || coleccion.size() == 0) {
            //throw new NoDataException("No hay datos");
        }
        return coleccion;
    }

    public boolean createDBLink(String name, String user, String pass, String ip, String port, String db) {
        String val = "CREATE DATABASE LINK " + name + " CONNECT TO " + user + " identified by " + pass + " USING '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = " + ip + ")(PORT = " + port + "))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = " + db + ")))'";

        System.out.println(val);
        boolean result = false;
        try {
            conectar(this.user, this.pass, this.ip, this.port, this.db);
            System.out.println("Connection to DB successful");
        } catch (SQLException | ClassNotFoundException ex) {
            Logger.getLogger(ServicioMenu.class.getName()).log(Level.SEVERE, null, ex);
        }

        PreparedStatement pstmt = null;
        try {
            pstmt = conexion.prepareStatement(val);
//            pstmt.setString(1,name);
//            pstmt.setString(2,user);
//            pstmt.setString(3,pass);
//            pstmt.setString(4,ip);
//            pstmt.setString(5,port);
//            pstmt.setString(6, db);
            result = pstmt.execute();
        } catch (SQLException ex) {
            Logger.getLogger(ServicioMenu.class.getName()).log(Level.SEVERE, null, ex);
        }

        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException ex) {
                Logger.getLogger(ServicioMenu.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        try {
            desconectar();
        } catch (SQLException ex) {
            Logger.getLogger(ServicioMenu.class.getName()).log(Level.SEVERE, null, ex);
        }
        return true;
    }

    public Collection listarUsers() {
        try {
            conectar(user, pass, ip, port, db);
        } catch (ClassNotFoundException ex) {
            //throw new GlobalException("No se ha localizado el Driver");
        } catch (SQLException e) {
            //throw new NoDataException("La base de datos no se encuentra disponible");
        }

        ResultSet rs = null;
        ArrayList coleccion = new ArrayList();
        Statement stmt = null;
        try {
            stmt = conexion.createStatement();
            rs = stmt.executeQuery(GET_USUARIOS);
            while (rs.next()) {
                String aux = rs.getString("username");
                if ((!(aux.equals("SYS"))) && (!(aux.equals("SYSTEM"))) && (!(aux.equals("ANONYMOUS")))) {
                    coleccion.add(aux);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();

            //throw new GlobalException("Sentencia no valida");
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                desconectar();
            } catch (SQLException e) {
                //throw new GlobalException("Estatutos invalidos o nulos");
            }
        }
        if (coleccion == null || coleccion.size() == 0) {
            //throw new NoDataException("No hay datos");
        }
        return coleccion;
    }

    public Collection listarRolesDeUsuario(String u) {
        try {
            conectar(user, pass, ip, port, db);
        } catch (ClassNotFoundException ex) {
            // throw new GlobalException("No se ha localizado el Driver");
        } catch (SQLException e) {
            //throw new NoDataException("La base de datos no se encuentra disponible");
        }

        ResultSet rs = null;
        ArrayList coleccion = new ArrayList();
        PreparedStatement stmt = null;
        try {
            stmt = conexion.prepareStatement(USERROL);
            stmt.setString(1, u);
            rs = stmt.executeQuery();
            while (rs.next()) {
                String aux = rs.getString("GRANTED_ROLE");
                coleccion.add(aux);
            }
        } catch (SQLException e) {
            e.printStackTrace();

            //throw new GlobalException("Sentencia no valida");
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                desconectar();
            } catch (SQLException e) {
                //throw new GlobalException("Estatutos invalidos o nulos");
            }
        }
        if (coleccion == null || coleccion.size() == 0) {
            //throw new NoDataException("No hay datos");
        }
        return coleccion;
    }

    public boolean iniciarConexion() {
        try {
            conectar(user, pass, ip, port, db);
            return true;
        } catch (ClassNotFoundException ex) {
            return false;
        } catch (SQLException ex) {
            Logger.getLogger(ServicioMenu.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }

    }

    public boolean removeCategoryFromUser(String cat, String us) throws GlobalException, NoDataException {
        try {
            conectar(user, pass, ip, port, db);
        } catch (ClassNotFoundException ex) {
            throw new GlobalException("No se ha localizado el Driver");
        } catch (SQLException e) {
            throw new NoDataException("La base de datos no se encuentra disponible");
        }

        ResultSet rs = null;
        Statement stmt = null;
        try {
            stmt = conexion.createStatement();
            String ss = "REVOKE " + cat + " FROM " + us;
            rs = stmt.executeQuery(ss);

        } catch (SQLException e) {
            //return false;
            e.printStackTrace();

            throw new GlobalException("Sentencia no valida");
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                desconectar();
                return true;
            } catch (SQLException e) {
                throw new GlobalException("Estatutos invalidos o nulos");
            }
        }

    }

    public ArrayList gettableTS(String ts) {

        try {

            conectar(user, pass, ip, port, db);
            System.out.println("Connection to DB successful");

        } catch (SQLException | ClassNotFoundException ex) {
            Logger.getLogger(ServicioMenu.class.getName()).log(Level.SEVERE, null, ex);
        }

        ResultSet rs = null;
        ArrayList list = new ArrayList();
        PreparedStatement stmt = null;
        try {
            stmt = conexion.prepareStatement(TABLES_TS);
            stmt.setString(1, ts);
            rs = stmt.executeQuery();
            while (rs.next()) {
                String aux = rs.getString("table_name");
                list.add(aux);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ServicioMenu.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                desconectar();
            } catch (SQLException e) {
                //throw new GlobalException("Estatutos invalidos o nulos");
            }
        }


        return list;
    }

    public ArrayList getColTable(String tb) {
        //JOptionPane.showMessageDialog(null, tb);
        try {

            conectar(user, pass, ip, port, db);
            System.out.println("Connection to DB successful");

        } catch (SQLException | ClassNotFoundException ex) {
            Logger.getLogger(ServicioMenu.class.getName()).log(Level.SEVERE, null, ex);
        }

        ResultSet rs = null;
        ArrayList list = new ArrayList();
        PreparedStatement stmt = null;
        try {
            stmt = conexion.prepareStatement(GET_TABLE_COLUMNS);
            stmt.setString(1, tb);
            rs = stmt.executeQuery();
            while (rs.next()) {
                String aux = rs.getString("column_name");
                list.add(aux);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ServicioMenu.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                desconectar();
            } catch (SQLException e) {
                //throw new GlobalException("Estatutos invalidos o nulos");
            }
        }


        return list;
    }

    public String getColType(String tb, String col) {
        //JOptionPane.showMessageDialog(null,tb+","+col);
        try {

            conectar(user, pass, ip, port, db);
            System.out.println("Connection to DB successful");

        } catch (SQLException | ClassNotFoundException ex) {
            Logger.getLogger(ServicioMenu.class.getName()).log(Level.SEVERE, null, ex);
        }
        String aux = "";
        ResultSet rs = null;
        PreparedStatement stmt = null;
        try {
            stmt = conexion.prepareStatement(COLUMN_TYPE);
            stmt.setString(1, tb);
            stmt.setString(2, col);
            rs = stmt.executeQuery();
            rs.next();
            aux = rs.getString("data_type");


        } catch (SQLException ex) {
            Logger.getLogger(ServicioMenu.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                desconectar();
            } catch (SQLException e) {
                //throw new GlobalException("Estatutos invalidos o nulos");
            }
        }


        return aux;
    }

    public boolean startLogminer(String logminer) throws GlobalException, NoDataException {
        try {
            conectar(user, pass, ip, port, db);
        } catch (ClassNotFoundException ex) {
            throw new GlobalException("No se ha localizado el Driver");
        } catch (SQLException e) {
            throw new NoDataException("La base de datos no se encuentra disponible");
        }

        ResultSet rs = null;
        boolean ret;
        Statement stmt = null;
        try {
            stmt = conexion.createStatement();
            ret = stmt.execute(logminer);

        } catch (SQLException e) {
            //return false;
            e.printStackTrace();

            throw new GlobalException("Sentencia no valida");
        }

        return ret;

    }

    public ArrayList<String> getLogminerResult(String lm) {

//        try {
//
//            conectar(user, pass, ip, port, db);
//            System.out.println("Connection to DB successful");
//
//        } catch (SQLException | ClassNotFoundException ex) {
//            Logger.getLogger(ServicioMenu.class.getName()).log(Level.SEVERE, null, ex);
//        }

        ResultSet rs = null;
        ArrayList<String> list = new ArrayList();
        PreparedStatement stmt = null;
        try {
            stmt = conexion.prepareStatement(lm);
            rs = stmt.executeQuery();
            while (rs.next()) {
                String aux = rs.getString(1);
                list.add(aux);
                aux = rs.getString(2);
                list.add(aux);
                aux = rs.getString(3);
                list.add(aux);
                aux = rs.getString(4);
                list.add(aux);
                aux = rs.getString(5);
                list.add(aux);
                aux = rs.getString(6);
                list.add(aux);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ServicioMenu.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                desconectar();
            } catch (SQLException e) {
                //throw new GlobalException("Estatutos invalidos o nulos");
            }
        }
        return list;
    }

    public Collection allTables() {
        try {
            conectar(user, pass, ip, port, db);
        } catch (ClassNotFoundException ex) {
            //throw new GlobalException("No se ha localizado el Driver");
        } catch (SQLException e) {
            //throw new NoDataException("La base de datos no se encuentra disponible");
        }

        ResultSet rs = null;
        ArrayList coleccion = new ArrayList();
        Statement stmt = null;
        try {
            stmt = conexion.createStatement();
            rs = stmt.executeQuery(this.queryForTables());
            while (rs.next()) {
                String aux = rs.getString("table_name");
                coleccion.add(aux);
//                aux = rs.getString("owner");
//                coleccion.add(aux);
            }
        } catch (SQLException e) {
            e.printStackTrace();

            //throw new GlobalException("Sentencia no valida");
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                desconectar();
            } catch (SQLException e) {
                //throw new GlobalException("Estatutos invalidos o nulos");
            }
        }
        if (coleccion == null || coleccion.size() == 0) {
            //throw new NoDataException("No hay datos");
        }
        return coleccion;
    }

    public String queryForTables() {
        String qry = "SELECT TABLE_NAME FROM ALL_TABLES";
        String concatenacion = "";
        ArrayList<String> tbs = this.getTS();
        for (int i = 0; i < tbs.size(); i++) {
            String ts = tbs.get(i);
            if (i == 0) {
                concatenacion = " WHERE ";
            } else {
                concatenacion += " OR ";
            }
            concatenacion = concatenacion + "TABLESPACE_NAME = '" + ts + "' ";
        }
//        JOptionPane.showMessageDialog(null,qry+concatenacion);
//        System.out.println(qry+concatenacion);
        return qry + concatenacion;
    }

    public ArrayList<String> getLogminerComp(String lm, Regla rule) {

        ResultSet rs = null;
        ArrayList<String> list = new ArrayList();
        PreparedStatement stmt = null;
        try {
            stmt = conexion.prepareStatement(lm);
            rs = stmt.executeQuery();
            while (rs.next()) {
                String aux = rs.getString(6);
                //JOptionPane.showMessageDialog(null, aux);
                System.out.println(aux);
                Scanner reader = new Scanner(aux);
                String sTexto = "";
                String a = "";
                String b = "";
                int cont = 1;
                while (reader.hasNext()) {
                    sTexto = reader.next();
                    //System.out.println(sTexto);
                    if (cont == 3) {
                        a = sTexto;
                    } else {
                        if (cont == 5) {
                            b = sTexto;
                        }
                    }
                    cont++;
                    //System.out.println(cont);
                }
                //this.evaluateCol(a, rule.getColummna());
                String info = this.getDatoColumna(b, this.evaluateCol(a, rule.getColummna()));
                boolean exito = this.evaluarRegla(info, rule);
                if (exito) {
                    aux = rs.getString(1);
                    list.add(aux);
                    aux = rs.getString(2);
                    list.add(aux);
                    aux = rs.getString(3);
                    list.add(aux);
                    aux = rs.getString(4);
                    list.add(aux);
                    aux = rs.getString(5);
                    list.add(aux);
                    aux = rs.getString(6);
                    list.add(aux);
                }
            }

        } catch (SQLException ex) {
            Logger.getLogger(ServicioMenu.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                desconectar();
            } catch (SQLException e) {
                //throw new GlobalException("Estatutos invalidos o nulos");
            }
        }
        return list;
    }

    public String createString(String s) {
        StringBuilder cadena = new StringBuilder();
        for (int n = 0; n < s.length(); n++) {
            char c = s.charAt(n);
            if (c == '"' || c == '\'') {
                cadena.append(String.format(" " + c + " "));
            } else {
                cadena.append(c);
            }
        }
        return cadena.toString();
    }

    public int evaluateCol(String ss, String col) {
        String fin = createString(ss);
        Scanner reader = new Scanner(fin);
        String sTexto = "";
        int cont = 0;
        int validacion = -1;
        boolean parentesis = false;
        while (reader.hasNext()) {
            sTexto = reader.next();
            if (parentesis) {
                if (!sTexto.equals(",") && !sTexto.equals("\"")) {
                    cont++;
                }
                //System.out.println(sTexto);
                if (sTexto.equals(col)) {
                    //JOptionPane.showMessageDialog(null,sTexto + " ; " + cont );
                    return cont;
                }
            } else {
                if (sTexto.equals("(")) {
                    parentesis = true;
                }
            }

        }
        return validacion;
    }

    public String getDatoColumna(String hilera, int col) {
        String fin = createString(hilera);
        Scanner reader = new Scanner(fin);
        String sTexto = null;
        int cont = 0;
        boolean parentesis = false;
        while (reader.hasNext()) {
            sTexto = reader.next();
            if (parentesis) {
                if (!sTexto.equals(",") && !sTexto.equals("\'")) {
                    cont++;
                }
                //System.out.println(sTexto);
                if (cont == col) {
                    //JOptionPane.showMessageDialog(null, "Dato: "+sTexto);
                    return sTexto;
                }
            } else {
                if (sTexto.equals("(")) {
                    parentesis = true;
                }
            }

        }
        return sTexto;
    }

    public boolean evaluarRegla(String dato, Regla ru) {
        String comparador = ru.antonimo();
        switch (comparador) {
            case ("<"): {
                int entero = Integer.parseInt(dato);
                int pred = Integer.parseInt(ru.getPredicado());
                if (entero < pred) {
                    return true;
                }
                return false;
            }
            case ("<="): {
                int entero = Integer.parseInt(dato);
                int pred = Integer.parseInt(ru.getPredicado());
                if (entero <= pred) {
                    return true;
                }
                return false;
            }
            case (">"): {
                int entero = Integer.parseInt(dato);
                int pred = Integer.parseInt(ru.getPredicado());
                if (entero > pred) {
                    return true;
                }
                return false;
            }
            case (">="): {
                int entero = Integer.parseInt(dato);
                int pred = Integer.parseInt(ru.getPredicado());
                if (entero >= pred) {
                    return true;
                }
                return false;
            }
            case ("="): {
                boolean exito = this.esNumero(ru.getPredicado());
                if (exito) {
                    int entero = Integer.parseInt(dato);
                    int pred = Integer.parseInt(ru.getPredicado());
                    if (entero == pred) {
                        return true;
                    }
                    return false;
                }else{
                    if(dato.equals(ru.getPredicado())){
                        return true;
                    }
                    return false;
                }
            }
            case ("!="): {
                boolean exito = this.esNumero(ru.getPredicado());
                if (exito) {
                    int entero = Integer.parseInt(dato);
                    int pred = Integer.parseInt(ru.getPredicado());
                    if (entero != pred) {
                        return true;
                    }
                    return false;
                }
                else{
                    if(!(dato.equals(ru.getPredicado()))){
                        return true;
                    }
                    return false;
                }
            }
        }
        return false;
    }

    public boolean esNumero(String hilera) {
        try {
            Double.parseDouble(hilera);
        } catch (Exception E) {
            return false;
        }
        return true;
    }
    
     public Collection listarUsers2(String server) {
        try {
            conectar(user, pass, ip, port, db);
        } catch (ClassNotFoundException ex) {
            //throw new GlobalException("No se ha localizado el Driver");
        } catch (SQLException e) {
            //throw new NoDataException("La base de datos no se encuentra disponible");
        }

        ResultSet rs = null;
        ArrayList coleccion = new ArrayList();
        Statement stmt = null;
        try {
            stmt = conexion.createStatement();
                        System.out.println("select username from dba_users@"+Control.getInstance().getSELECTED_SERVER()+" where lock_date is null order by username");

            rs = stmt.executeQuery("select username from dba_users@"+server+" where lock_date is null order by username");
            while (rs.next()) {
                String aux = rs.getString("username");
                if ((!(aux.equals("SYS"))) && (!(aux.equals("SYSTEM"))) && (!(aux.equals("ANONYMOUS")))) {
                    coleccion.add(aux);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();

            //throw new GlobalException("Sentencia no valida");
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                desconectar();
            } catch (SQLException e) {
                //throw new GlobalException("Estatutos invalidos o nulos");
            }
        }
        if (coleccion == null || coleccion.size() == 0) {
            //throw new NoDataException("No hay datos");
        }
        return coleccion;
    }
}
